import DbUtil
import GenLog
import SqlCmd
import re
import SecretTool
from config import api_config, hr_config

su = SecretTool.SecertUtil()
dehruser, dehrpwd = su.decrypt(hr_config.userName, hr_config.passWord)
deapiuser, deapipwd = su.decrypt(api_config.userName, api_config.passWord)
# 映射表相关用户名密码加密
deapiuser2, deapipwd2 = su.decrypt(api_config.userName2, api_config.passWord2)

# 新建关于hr库的数据库对象
hrdb = DbUtil.DbUtil(hr_config.serverName, dehruser, dehrpwd, hr_config.dataBase)
hrdb.get_conn()

# 映射表相关数据库对象
mapdb = DbUtil.DbUtil(api_config.serverName2, deapiuser2, deapipwd2, api_config.dataBase2)
mapdb.get_conn()

# 新建关于API库的数据库对象
apidb = DbUtil.DbUtil(api_config.serverName, deapiuser, deapipwd, api_config.dataBase)
apidb.get_conn()

# 从HR库获取新入职员工 工号 姓名 一级部门 二级部门 职位 相关信息
rs_newcomer = hrdb.query(SqlCmd.sqlcmd_NewDispatchComerToday)

# 新入职员工生成器
def newerange():
    for tmp_list in rs_newcomer[0]:
        yield tmp_list

# hrmapapi表中的HR_DEPTNAME列表化
rs_hrdeptname = mapdb.query('SELECT ID,HR_DEPTNAME,HR_POSITION FROM dbo.autoops_hrmapapi')
# print(rs_hrdeptname)

# 根据HR组织架构查找API组织架构
def QueryAPIOrg(zPrimaryDept, zDeptName, zPositions):
    # 轮询HR deptname，例：信息部\硬件运维\系统运维组
    for tmp_hrdept in rs_hrdeptname[0]:
        # 匹配第一个\前字符串
        matchObj1 = re.match(r'^(\w+)', tmp_hrdept['HR_DEPTNAME'])
        group1 = matchObj1.group()
        # 匹配最后一个\后字符串
        matchObj2 = re.match(r'(.*)\\(\S+)$', tmp_hrdept['HR_DEPTNAME'])
        # 排除没有\的部门字符串情况
        if matchObj2 is None:
            group2 = group1
        else:
            group2 = matchObj2.group(2)
        if (group1 == zPrimaryDept and group2 == zDeptName) or (group1 + '（停用）' == zPrimaryDept and group2 == zDeptName) or (group1 + '（停用）' == zPrimaryDept and group2 + '（停用）' == zDeptName):
            list_hrdept = tmp_hrdept['HR_POSITION'].split('/')
            for tmp_list_hrdept in list_hrdept:
                if zPositions == tmp_list_hrdept:
                    # print(tmp_hrdept['ID'])
                    return tmp_hrdept['ID']
    else:
        # print('无department')
        return 0

# 添加员工
def InsertApi(cWorkNo, cName, cDeptName, cPostid, cPostName, Lead_UserID):
    # 宣布全局变量
    global affcted_count

    # 判断工号是否已存在
    rs_queryworkno = apidb.query(SqlCmd.sqlcmd_queryworkno.format(cWorkNo))
    if(rs_queryworkno[1] != 0):
        GenLog.GenLog(cWorkNo + '工号已存在,无需重复插入\n')
        return -1
    # ***数据库执行插入操作***
    try:
        apidb.execute(SqlCmd.sqlcmd_insertapi.format(cWorkNo, cName, cDeptName, cPostid, cPostName, Lead_UserID))
        GenLog.GenLog('【工号：' + cWorkNo + '】插入数据库成功！\n')
        affcted_count = affcted_count + 1
    except Exception as e:
        GenLog.GenLog(cWorkNo + '该工号插入数据库异常\n')

# 添加角色
def InsertRoles(userid, roleid):
    rs_isBindRecur = apidb.query(SqlCmd.sqlcmd_isBindRecur.format(userid, roleid))
    # 不存在绑定则进行绑定操作
    if rs_isBindRecur[1] == 0:
        # ***数据库执行插入操作***
        try:
            apidb.execute(SqlCmd.sqlcmd_insertAPIrole.format(userid, roleid))
            GenLog.GenLog('【userid:' + userid + ' roleid:' + roleid + '】角色绑定成功！\n')
        except Exception as e:
            GenLog.GenLog(roleid + '该角色ID插入数据库异常\n')

# 初始化计数值
affcted_count = 0

def main():
    # HR新入职名单中取出
    for list_tmp in newerange():
        # 初始化ID值
        DeptID = ''
        PostID = ''
        PostName = ''
        LeaderID = ''

        # 取出HRMS对应人员信息
        cWorkNo = str(list_tmp['cWorkNo'])
        cName = list_tmp['cName']
        cPrimaryDept = list_tmp['cPrimaryDept']
        cDeptName = list_tmp['cDeptName']
        cPosition = list_tmp['cPosition']
        # print(cPrimaryDept, cDeptName, cPosition) # 打印一级部门、二级部门及岗位

        # 获取hr_map_api表ID
        rs_QueryMapID = QueryAPIOrg(cPrimaryDept, cDeptName, cPosition)

        # 未匹配人员信息进行输出
        if not rs_QueryMapID:
            GenLog.GenLog('''【工号：{}】未有对应部门，请将【一级部门：'{}' 末级部门：'{}' 岗位：'{}'】加入hr_map_api对应组织架构中\n'''.format(cWorkNo, cPrimaryDept, cDeptName, cPosition))
            continue

        # 通过ID查找对应API组织架构
        rs_queryapi = mapdb.query(SqlCmd.sqlcmd_queryapi.format(rs_QueryMapID))

        # 查找deptID
        zDeptName = '无锡先导智能装备股份有限公司\\' + rs_queryapi[0][0]['API_DEPTNAME']
        rs_queryDeptID = apidb.query(SqlCmd.sqlcmd_queryDeptID.format(zDeptName))
        # *DeptID*
        DeptID = rs_queryDeptID[0][0]['deptId']
        # print(zDeptName, DeptID)
        # print(DeptID)

        # *postname*
        PostName = rs_queryapi[0][0]['API_POSITION']

        # 查找postid
        rs_queryPostID = apidb.query(SqlCmd.sqlcmd_queryPostID.format(DeptID, PostName))
        # *PostID*
        PostID = rs_queryPostID[0][0]['ID']
        # print(PostID, PostName)

        # 查找LeaderID
        rs_queryLeaderID = mapdb.query(SqlCmd.sqlcmd_queryLeaderID.format(rs_QueryMapID))
        LeaderID = rs_queryLeaderID[0][0]['API_LEADERID']
        # *LeaderID*
        # print(LeaderID + '\n' + '----------------------------')

        # ***数据库执行插入操作***
        InsertApi(cWorkNo, cName, DeptID, PostID, PostName, LeaderID)

        # 查找API userid
        rs_queryUserID = apidb.query(SqlCmd.sqlcmd_queryUserID.format(cWorkNo))
        UID = rs_queryUserID[0][0]['ID']

        # 查找API roleid
        rs_queryApiRoles = mapdb.query(SqlCmd.sqlcmd_queryAPIrole.format(rs_QueryMapID))
        # 如果hr_map_api中的角色内容为空则跳过后续循环内容
        if rs_queryApiRoles[1] == 0:
            continue
        # 打印hr_map_api中的角色内容
        # print(rs_queryApiRoles)
        ApiRoles = rs_queryApiRoles[0][0]['API_ROLES'].split(';')
        # 轮询hr_map_api匹配角色，一一绑定
        for tmp_ApiRoles in ApiRoles:
            rs_queryApiRoleID = apidb.query(SqlCmd.sqlcmd_queryApiRoleID.format(tmp_ApiRoles))
            if rs_queryApiRoleID[1] == 0:
                # 不存在的角色进行记录并跳过该次循环
                GenLog.GenLog('【角色:' + tmp_ApiRoles + '】不存在,请新建该角色\n')
                continue
            RID = rs_queryApiRoleID[0][0]['ID']

            # ***数据库执行绑定操作***
            InsertRoles(UID, RID)

    GenLog.GenLog('---插入成功账号数量{}个---'.format(affcted_count))

if __name__ == '__main__':
    main()